'''
使用openpyxl在excel中写入10列2000行的数据，然后使用openpyxl读取出来并使用pymysql写入到数据库中
'''


import openpyxl
import pymysql
from datetime import datetime

# workbook = openpyxl.load_workbook('user.xlsx') # 建立一个表格
# workbook.create_sheet('one sheet')   # 建立一个sheet页
# worksheet = workbook['one sheet']
#
# worksheet['A1'] = 'id'
# worksheet['B1'] = 'idcard'
# worksheet['C1'] = 'username'
# worksheet['D1'] = 'realname'
# worksheet['E1'] = 'pwd'
# worksheet['F1'] = 'telphone'
# worksheet['G1'] = 'email'
# worksheet['H1'] = 'age'
# worksheet['I1'] = 'sex'
# worksheet['J1'] = 'address'
# worksheet['K1'] = 'hiredate'
# worksheet['L1'] = 'sal'
# worksheet['M1'] = 'job'
# worksheet['N1'] = 'company'
#
# data = [
#     [100,
#     '123',
#     'Lucy',
#     'LXY',
#     '123456',
#     '987654321',
#     '123@163.com',
#     20,
#     'woman',
#     'china',
#     '8.1',
#     '20000',
#     'computer',
#     'Nikki']
# ]
# # 设置一个数据，循环输入2000次
# for num in range(0,2000):
#     for i in data:
#         worksheet.append(i)
#
# workbook.save('user.xlsx')   # 保存表格

# 读取出来
data_read = openpyxl.load_workbook('user.xlsx')
sheet = data_read.active

db = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='123456',
    database='practice',
    port=3306,
    charset='utf8'
)


try:
    with db.cursor()as cursos:
        sql_create = '''
CREATE TABLE `user_info` (
  `id` int(11) ,
  `idcard` varchar(50) ,
  `username` varchar(50) ,
  `realname` varchar(50) ,
  `pwd` varchar(50) ,
  `telphone` varchar(12) ,
  `email` varchar(100) ,
  `age` int(11) ,
  `sex` varchar(20) ,
  `address` varchar(200) ,
  `hiredate` date ,
  `sal` double(9,2) ,
  `job` varchar(100) ,
  `company` varchar(100)
) ;
        '''
        sql = 'insert into user_info(id, idcard, username, realname, pwd, telphone,email, age, sex, address, hiredate, sal, job, company) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
        for row in range(2, sheet.max_row + 1):
            # 提取每行数据
            data = (
                sheet.cell(row=row, column=1).value,  # id
                sheet.cell(row=row, column=2).value,  # idcard
                sheet.cell(row=row, column=3).value,  # username
                sheet.cell(row=row, column=4).value,  # realname
                sheet.cell(row=row, column=5).value,  # pwd
                sheet.cell(row=row, column=6).value,  # telphone
                sheet.cell(row=row, column=7).value,  # email
                sheet.cell(row=row, column=8).value,  # age
                sheet.cell(row=row, column=9).value,  # sex
                sheet.cell(row=row, column=10).value,  # address
                # 处理日期格式
                datetime.strptime(sheet.cell(row=row, column=11).value, '%m.%d').replace(year=datetime.now().year)
                if sheet.cell(row=row, column=11).value else None,
                sheet.cell(row=row, column=12).value,  # sal
                sheet.cell(row=row, column=13).value,  # job
                sheet.cell(row=row, column=14).value  # company
            )
            cursos.execute(sql, data)

        #cursos.execute(sql_create)

        db.commit()
        print('数据插入成功')
except Exception as e:
    print('修改失败',e)
finally:
    db.close()

